package controllers;

import java.sql.*;
import java.util.ArrayList;

import model.*;
import play.db.DB;

public class DatabaseController {
	
	/**
	 * Database controller for database
	 */
	public DatabaseController()
	{	
	}
	
	/**
	 * Takes list of movies from database
	 * @return list of movies taken from database
	 */
	public ArrayList<Movie> GetMoviesFromDB()
	{
    	ArrayList <Movie> Movies = new ArrayList<Movie>();
		Connection c = null;
	    try {
	    	//establish connection
	    	c=DB.getConnection();
	    	//get results - use statement
	    	ResultSet rs = c.createStatement().executeQuery( "SELECT * FROM movie;" );
	    	//iterate through results
	    	while ( rs.next() ) {
	    		//assign to model
	    		Movie m = new Movie(rs.getInt("ID"), rs.getString("Title"), rs.getInt("TMDBID"));
	    		Movies.add(m);		        
	    	}
	    	rs.close();
	    	c.close();	    
	    }
	    //exception handling
	    catch ( Exception e ) {
	      System.err.println("Database error in obtaining movies"+e.toString());
	      System.exit(0);
	    }
	    return Movies;
	}
	
	public Integer GetPeopleCount(){
		Connection c = null;
		Integer counter = 0;
		
		try{
			c = DB.getConnection();
			
			ResultSet rs = c.createStatement().executeQuery( "SELECT COUNT(*) FROM person;" );		
			if(rs.next())
			{
			counter = rs.getInt(1);
			}
			rs.close();
			c.close();
		}
		catch(Exception e){
			System.err.println("Database error in obtaining people count. "+e.toString());
		    System.exit(0);
		}
		return counter;		
	}
	
	/**
	 * Takes person from db
	 * @param description - description of person
	 * @return single person that match the description
	 */
	public Person GetPersonFromDB(String description)
	{
		Person p = null;
		Connection c = null;
	    try {
	    	//establish connection
	    	c=DB.getConnection();
	    	//get results - use statement
	    	ResultSet rs = c.createStatement().executeQuery( "SELECT * FROM person WHERE Description = \'"+description+"\';" );
	    	//iterate through results
	    	while ( rs.next() ) {
	    		//assign to model
	    		p = new Person(rs.getInt("ID"), rs.getString("Description"));	    				        
	    	}
	    	rs.close();
	    	c.close();	    	
	    }
	    //exception handling
	    catch ( Exception e ) {
	      System.err.println("Database error in obtaining person. "+e.toString());
	      System.exit(0);
	    }	    
	    return p;
	}
	
	/**
	 * gets single evaluation from db
	 * @param MovieID
	 * @param PersonID
	 * @return single evaluation for given movie evaluated by given person
	 */
	public Evaluation GetSingleEvaluationFromDB(int MovieID, int PersonID){
		Evaluation e = null;
		Connection c = null;
	    try {  
	    	//establish connection
	    	c=DB.getConnection();
	    	//get results - use statement
	    	ResultSet rs = c.createStatement().executeQuery( "SELECT * FROM evaluation WHERE MovieID = "+MovieID+" AND PersonID ="+PersonID+";" );
	    	//iterate through results
	    	while ( rs.next() ) {
	    		//assign to model
	    		e = new Evaluation(rs.getInt("ID"), rs.getInt("MovieID"), rs.getInt("PersonID"), rs.getInt("Evaluation"));	    				        
	    	}
	    	rs.close();
	    	c.close();	    	
	    }
	    //exception handling
	    catch ( Exception exc) {
	      System.err.println("Database error in obtaining evaluation. "+exc.toString());
	      System.exit(0);
	    }	    
	    return e;
	}
	
	/**
	 * gets list of evaluations for given person
	 * @param personID
	 * @return list of evaluations
	 */
	public ArrayList<Evaluation> GetEvaluationsForPersonFromDB(int personID){
		ArrayList<Evaluation> evaluations = new ArrayList<Evaluation>();
		Connection c = null;
	    try {  
	    	//establish connection
	    	c=DB.getConnection();
	    	//get results - use statement
	    	ResultSet rs = c.createStatement().executeQuery( "SELECT * FROM evaluation WHERE PersonID ="+personID+";" );
	    	//iterate through results
	    	while ( rs.next() ) {
	    		//assign to model
	    		Evaluation e = new Evaluation(rs.getInt("ID"), rs.getInt("MovieID"), rs.getInt("PersonID"), rs.getInt("Evaluation"));
	    		evaluations.add(e);
	    	}
	    	rs.close();
	    	c.close();	    	
	    }
	    //exception handling
	    catch ( Exception exc) {
	      System.err.println("Database error in obtaining evaluations. "+exc.toString());
	      System.exit(0);
	    }	    
	    return evaluations;
	}
		
	/**
	 * inserts person to db
	 * @param p - person
	 */
	public void InsertPersonToDB(Person p)
	{
		if(p == null)
			return;
		    	
		if((Person)(this.GetPersonFromDB(p.Description)) != null ){
			System.out.println("Such user already exists in DB!");
		}
		else{			
			Connection c = null;
			Statement st = null;
			
			try{
				c = DB.getConnection();
				st = c.createStatement();
				st.executeUpdate("Insert into person (Description) Values (\'"+p.Description+"\');");
				
				st.close();
				c.close();
				
				
			}
			catch(Exception e){
				 System.err.println("Database error in inserting person. "+e.toString());
			     System.exit(0);
			}			
			
		}
	}
	
	/**
	 * inserts evaluation to db
	 * @param e - evaluation
	 */
	public void InsertEvaluationToDB(Evaluation e){
		if(e == null)
			return;
				
		Connection c = null;
		Statement st = null;
			
		try{
			c = DB.getConnection();
			st = c.createStatement();
			st.executeUpdate("INSERT INTO evaluation (PersonID, MovieID, Evaluation) VALUES ( "+e.PersonId+","+e.MovieId+","+e.Evaluation+");");	
			st.close();
			c.close();
			
		}
		catch(Exception exc){
			 System.err.println("Database error in inserting evaluation. "+exc.toString());
		     System.exit(0);
		}			
	}
	
	/**
	 * 
	 * @param id
	 * @param Evaluation
	 */
	public void UpdateEvaluationToDB(int id, Integer Evaluation){
		Connection c = null;
		Statement st = null;
			
		try{
			c = DB.getConnection();
			st = c.createStatement();
			st.executeUpdate("UPDATE evaluation SET Evaluation = "+Evaluation+" WHERE ID ="+id+ ";");
			
			st.close();
			c.close();
			
		}
		catch(Exception exc){
			 System.err.println("Database error in updating evaluation. "+exc.toString());
		     System.exit(0);
		}
	}
}
